package com.cfs.daoimpl.sys;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.RowMapper;

import com.cfs.po.Exp;
import com.cfs.po.sys.Menu;
import com.cfs.po.sys.Right;

/**
 * 用户管理dao
 * 
 * @author SDZJ
 * 
 */
public class MenuDao extends Dao {
	
	private class SQL{
		public static final String INS = "INSERT INTO TB_MENUS(ID, PID, NAME, PAGE) VALUES(?, ?, ?, ?)";
		public static final String DEL_BY_ID = "DELETE FROM TB_MENUS WHERE ID=?";
		public static final String DEL_BY_PID = "DELETE FROM TB_MENUS WHERE PID=?";
		public static final String UPDATE_BY_ID = "UPDATE TB_MENUS SET PID=?, NAME=?, PAGE=? WHERE ID=?";

		public static final String QRY_ALL = "SELECT * FROM TB_MENUS WHERE PID IS NULL ORDER BY ID";
		public static final String QRY_BY_NAME = "SELECT * FROM TB_MENUS WHERE NAME=?";
		public static final String QRY_BY_ID = "SELECT * FROM TB_MENUS WHERE ID=?";
		public static final String QRY_BY_PID = "SELECT * FROM TB_MENUS WHERE PID=?";

		public static final String QRY_BY_ROLE = "SELECT * FROM TB_MENUS M LEFT JOIN TB_MENU_ROLE R ON R.MENUID=M.ID WHERE R.ROLEID=? ORDER BY M.ID";

		public static final String INS_MENU_RIGHT = "INSERT INTO TB_MENU_RIGHT(MENUID, RIGHTID) VALUES(?, ?)";
		public static final String DEL_MENU_RIGHT = "DELETE FROM TB_MENU_RIGHT WHERE MENUID=? AND RIGHTID=?";
		public static final String QRY_RIGHT_BY_MENUID = "SELECT * FROM TB_MENU_RIGHT MR LEFT JOIN TB_RIGHT R ON MR.RIGHTID=R.ID WHERE MR.MENUID=? ORDER BY MR.RIGHTID ASC";
		public static final String QRY_RIGHT_ALL = "SELECT * FROM TB_RIGHTS ORDER BY ID ASC";

		public static final String DEL_MENU_ROLE = "DELETE FROM TB_MENU_ROLE WHERE ROLEID=? AND MENUID=?";
		public static final String DEL_MENU_ROLE_BY_ROLE = "DELETE FROM TB_MENU_ROLE WHERE ROLEID=?";
		public static final String DEL_MENU_ROLE_BY_MENU = "DELETE FROM TB_MENU_ROLE WHERE MENUID=?";
		public static final String INS_MENU_ROLE = "INSERT INTO TB_MENU_ROLE(ROLEID, MENUID) VALUES(?,?)";
	}

	private RowMapper<Menu> rowMapper = new RowMapper<Menu>() {

		@Override
		public Menu mapRow(ResultSet rs, int arg1) throws SQLException {
			System.out.println("RS: " + rs + " - " + arg1);
			Menu menu = new Menu();
			menu.setId(rs.getLong("ID"));
			menu.setPid(rs.getLong("PID"));
			menu.setName(rs.getString("NAME"));
			menu.setPage(rs.getString("PAGE"));
			menu.setTag(rs.getInt("TAG"));

			menu.setSubMenu(subMenu(menu.getId()));

			return menu;
		}

	};
	
	private RowMapper<Right> rightMapper = new RowMapper<Right>() {

		@Override
		public Right mapRow(ResultSet rs, int arg1) throws SQLException {
			System.out.println("RS: " + rs + " - " + arg1);
			Right right = new Right();
			right.setId(rs.getLong("ID"));
			right.setName(rs.getString("NAME"));
			right.setUri(rs.getString("URI"));

			return right;
		}
	};
	

	/**
	 * 添加用户菜单
	 * 
	 * @param users
	 * @return
	 */
	public Menu add(Menu m) {
		String sql = SQL.INS;

		Menu menu = new Menu();

		try {
			int st = jdbcTemplate.update(sql, m.getId(), m.getPid(), m.getName().trim(), m.getPage()==null?null:m.getPage().trim());
			menu = queryByName(m.getName());

			this.updateMenuRights(menu.getId(), menu.getRightsList() );
		} catch (DuplicateKeyException e) {
//			e.printStackTrace();
			menu.setExp(new Exp("关键数据项重复: " + m.getId() + ", " + m.getName()));
		} catch (Exception e) {
//			System.err.println("EEEEE: " + e);
			e.printStackTrace();
			menu.setExp(new Exp("数据错误:" + e.getMessage()));
		}

		return menu;
	}

	public Menu queryById(Long id) {
		String sql = SQL.QRY_BY_ID;
		List<Menu> menuList = jdbcTemplate.query(sql, new Object[]{ id }, rowMapper);

		if(menuList!=null){
			for(Menu m : menuList){
				m.setRightsList(queryRightsById(m.getId()));
			}
		}
		
		return menuList == null ? null : menuList.get(0);
	}

	public Menu queryByName(String name) {
		String sql = SQL.QRY_BY_NAME;
		List<Menu> userList = jdbcTemplate.query(sql, new Object[]{ name }, rowMapper);
		return userList == null ? null : userList.get(0);
	}

	/**
	 * 查询子菜单
	 * 
	 * @param pid
	 * @return
	 */
	private List<Menu> subMenu(Long pid){
		return jdbcTemplate.query(SQL.QRY_BY_PID, new Object[]{ pid }, rowMapper);
	}

//
//	public Menu queryByPid(Long pid) {
//		String sql = DB_SQL.MENU_QRY_BY_PID;
//		List<Menu> userList = jdbcTemplate.query(sql, new Object[]{ pid }, rowMapper);
//		return userList == null ? null : userList.get(0);
//	}

	/**
	 * 据角色 ID 查菜单列表 
	 * 
	 * @param rid
	 * @return
	 */
	public List<Menu> queryByRoleId(Long rid) {
		
		List<Menu> list = null;
		if(rid>0){
			list = jdbcTemplate.query(SQL.QRY_BY_ROLE, new Object[]{ rid }, rowMapper);
		}else{
			list = jdbcTemplate.query(SQL.QRY_ALL, rowMapper);
		}

		return list;
	}

	/**
	 * 查询菜单权限
	 * 
	 * @param mid
	 * @return
	 */
	public List<Right> queryRightsById(Long mid) {

		List<Right> list = null;
		if(mid>0){
			list = jdbcTemplate.query(SQL.QRY_RIGHT_BY_MENUID, new Object[]{ mid }, rightMapper);
		}else{
			list = jdbcTemplate.query(SQL.QRY_RIGHT_ALL, rightMapper);
		}

		return list;
	}

	/**
	 * 删除菜单
	 * 
	 * @param ids
	 * @return
	 */
	public int deleteById(Long[] ids) {
		String sql = SQL.DEL_BY_ID;

		List<Object[]> paramList = new ArrayList<Object[]>();
		for (long id : ids) {
			paramList.add(new Object[] { id });
		}

		int[] rows = jdbcTemplate.batchUpdate(sql, paramList);

		int p = 0;
		for (int x : rows) {
			if (x == 1) {
				p++;
			}
		}

		return p;
	}

	/**
	 * 更新菜单
	 * 
	 * @param menu
	 * @return
	 */
	public Menu updateMenu(Menu menu) {
		String sql = SQL.UPDATE_BY_ID;

		try {
			int st = jdbcTemplate.update(sql,  menu.getPid(), menu.getName().trim(), menu.getPage()!=null?menu.getPage().trim():null, menu.getId());
			Menu m = this.queryById(menu.getId());

			this.updateMenuRights(menu.getId(), menu.getRightsList() );
		} catch (Exception e) {
			menu.setExp(new Exp("数据错误:" + e.getMessage()));
			e.printStackTrace();
		}

		return menu;
	}

	/**
	 * 更新菜单权限列表
	 * 
	 * @param mid
	 * @param rl
	 * @return
	 */
	public int updateMenuRights(Long mid, List<Right> rl) {

		List<Right> list = this.queryRightsById(mid);
		
		List<Long> aList = new ArrayList<Long>();
		if(rl!=null)
		for(Right r : rl){
			aList.add(r.getId());
			for(Right rn : list){
				if(r.getId().longValue() == rn.getId().longValue()){
					aList.remove(r.getId());
					list.remove(rn);
					break;
				}
			}
		}

		List<Object[]> dpl = new ArrayList<Object[]>();
		for(Right r : list){
			dpl.add(new Object[]{mid, r.getId()});
		}
		int[] di = jdbcTemplate.batchUpdate(SQL.DEL_MENU_RIGHT, dpl);

		List<Object[]> ipl = new ArrayList<Object[]>();
		for(Long l : aList){
			// add
			ipl.add(new Object[]{mid, l});
		}
		int[] ii = jdbcTemplate.batchUpdate(SQL.INS_MENU_RIGHT, ipl);

		return di.length + ii.length;
	}

	/**
	 * 更新角色菜单
	 * 
	 * @param rid
	 * @param mids
	 * @return
	 */
	public int updateRoleMenus(Long rid, Long[] mids) {
//		System.err.println("Update Role-Menu: " + rid + " menus:" + mids);

		List<Menu> list = this.queryByRoleId(rid);
		
		List<Long> nList = new ArrayList<Long>();
		if(mids!=null)
		for(Long mid : mids){
			nList.add(mid);
			for(Menu m : list){
				if(m.getId().equals(mid)){
					nList.remove(mid);
					list.remove(m);
					break;
				}
			}
		}

		List<Object[]> dpl = new ArrayList<Object[]>();
		for(Menu m : list){
			// delete
			dpl.add(new Object[]{rid, m.getId()});
		}
		int[] di = jdbcTemplate.batchUpdate(SQL.DEL_MENU_ROLE, dpl);
		
		List<Object[]> ipl = new ArrayList<Object[]>();
		for(Long l : nList){
			// add
			ipl.add(new Object[]{rid, l});
		}
		int[] ii = jdbcTemplate.batchUpdate(SQL.INS_MENU_ROLE, ipl);

		return di.length + ii.length;
	}








}
